﻿using HowToEatMeat.Entities;
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

namespace HowToEatMeat
{
    public class DataBase
    {
        private static string ConnectionString = ".\\Data.db";
        public static Record AddOne(double value, string remark, DateTime date)
        {
            using (LiteDB.LiteDatabase db = new LiteDB.LiteDatabase(ConnectionString))
            {
                try
                {
                    var result = new Record() { Value = value, Remark = remark, Date = date };
                    var col = db.GetCollection<Record>();
                    result._id = col.Insert(result);
                    db.Commit();
                    return result;
                }
                catch (Exception)
                {
                    return null;
                }
            }
        }

        public static bool SetPassWord(string passWord,out string msg)
        {
            if (!string.IsNullOrEmpty(passWord))
            {
                ConnectionString = $"FileName=.\\Data.db;PassWord={passWord};";
            }
            try
            {
                using (LiteDB.LiteDatabase db = new LiteDB.LiteDatabase(ConnectionString))
                {
                    if (!db.CollectionExists("Record"))
                    {
                        var col = db.GetCollection<Record>();
                        col.EnsureIndex(m => m.Date);
                        db.Commit();
                    }
                }
                msg = string.Empty;
                return true;
            }
            catch (Exception ex)
            {
                msg = ex.Message;
                return false;
            }
        }

        public static double SumAllReacord()
        {
            using (LiteDB.LiteDatabase db = new LiteDB.LiteDatabase(ConnectionString))
            {
                try
                {
                    var colCaculation = db.GetCollection<Caculation>();
                    var totalOfAll = colCaculation.FindById("TotalOfAll");
                    if (totalOfAll == null)
                    {
                        var colRecord = db.GetCollection<Record>();
                        var query = new LiteDB.Query();
                        query.Select.Fields.Add("Value");
                        totalOfAll = new Caculation() { _id = "TotalOfAll", Value = colRecord.Find(query).Sum(m => m.Value) };
                        colCaculation.Insert(totalOfAll);
                        db.Commit();
                    }
                    return totalOfAll.Value;
                }
                catch (Exception)
                {
                    return 0d;
                }
            }
        }

        public static List<Record> GetRecords(DateTime date, out double totalOfDay, out string dayString)
        {
            using (LiteDB.LiteDatabase db = new LiteDB.LiteDatabase(ConnectionString))
            {
                dayString = $"{date.Year:D04}-{date.Month:D02}-{date.Day:D02}";
                try
                {
                    var colCaculation = db.GetCollection<Caculation>();
                    var colRecord = db.GetCollection<Record>();
                    var result = colRecord.Find(LiteDB.Query.EQ("Date", date.Date)).ToList();
                    var tod = colCaculation.FindById(dayString);
                    if (tod == null)
                    {
                        tod = new Caculation { _id = dayString, Value = result.Sum(m => m.Value) };
                        colCaculation.Insert(tod);
                        db.Commit();
                    }
                    totalOfDay = tod.Value;
                    return result;
                }
                catch (Exception)
                {
                    totalOfDay = 0;
                    return null;
                }
            }
        }

        public static bool ReomveOne(LiteDB.ObjectId id)
        {
            using (LiteDB.LiteDatabase db = new LiteDB.LiteDatabase(ConnectionString))
            {
                try
                {
                    var col = db.GetCollection<Record>();
                    var result = col.Delete(id);
                    db.Commit();
                    return result;
                }
                catch (Exception)
                {
                    return false;
                }
            }
        }

        public static bool SetCaculation(string key, double value)
        {
            using (LiteDB.LiteDatabase db = new LiteDB.LiteDatabase(ConnectionString))
            {
                try
                {
                    var colCaculation = db.GetCollection<Caculation>();
                    var cac = new Caculation() { _id = key, Value = value };
                    colCaculation.Upsert(cac);
                    db.Commit();
                    return true;
                }
                catch (Exception)
                {
                    return false;
                }
            }
        }

        public static double GetMonthsIn(DateTime time, out string monthInString)
        {
            monthInString = $"{time.Year:D04}-{time.Month:D02}-IN";
            using (LiteDB.LiteDatabase db = new LiteDB.LiteDatabase(ConnectionString))
            {
                try
                {
                    var colCaculation = db.GetCollection<Caculation>();
                    var monthIn = colCaculation.FindById(monthInString);
                    if (monthIn == null)
                    {
                        var colRecord = db.GetCollection<Record>();
                        var query = new LiteDB.Query();
                        query.Select.Fields.Add("Value");
                        query.Where.Add(
                            LiteDB.Query.And(
                                LiteDB.Query.GTE("Date", new DateTime(time.Year, time.Month, 1)),
                                LiteDB.Query.LTE("Date", new DateTime(time.Year, time.Month, DateTime.DaysInMonth(time.Year, time.Month))),
                                LiteDB.Query.GT("Value", 0)
                                )
                            );
                        monthIn = new Caculation() { _id = monthInString, Value = colRecord.Find(query).Sum(m => m.Value) };
                        colCaculation.Insert(monthIn);
                        db.Commit();
                    }
                    return monthIn.Value;
                }
                catch (Exception)
                {
                    return 0d;
                }
            }
        }

        public static double GetMonthsOut(DateTime time,out string monthOutString)
        {
            monthOutString = $"{time.Year:D04}-{time.Month:D02}-OUT";
            using (LiteDB.LiteDatabase db = new LiteDB.LiteDatabase(ConnectionString))
            {
                try
                {
                    var colCaculation = db.GetCollection<Caculation>();
                    var monthOut = colCaculation.FindById(monthOutString);
                    if (monthOut == null)
                    {
                        var colRecord = db.GetCollection<Record>();
                        var query = new LiteDB.Query();
                        query.Select.Fields.Add("Value");
                        query.Where.Add(
                            LiteDB.Query.And(
                                LiteDB.Query.GTE("Date", new DateTime(time.Year, time.Month, 1)),
                                LiteDB.Query.LTE("Date", new DateTime(time.Year, time.Month, DateTime.DaysInMonth(time.Year, time.Month))),
                                LiteDB.Query.LT("Value", 0)
                                )
                            );
                        monthOut = new Caculation() { _id = monthOutString, Value = Math.Abs(colRecord.Find(query).Sum(m => m.Value)) };
                        colCaculation.Insert(monthOut);
                        db.Commit();
                    }
                    return monthOut.Value;
                }
                catch (Exception)
                {
                    return 0d;
                }
            }
        }
    }
}